package org.gesplan.util;

import java.io.File;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import jxl.SheetSettings;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.Pattern;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.gesplan.ui.MainClass;

/**
 *
 * @author David
 */
public class ExcelManager {

    private static Logger logger;
    private static Workbook workbook = null;
    private static WritableWorkbook copy = null;
    private static WritableSheet sheet = null;
    private static Number cellNumber = null;
    private static Label cellLabel = null;
    private static String in = null;
    private static String out = null;
    private static Object[] params;    
    private static final int YEAR_FIRST = 2013;
    private static final int MONTHLY_FIT_HEIGHT = 5;
    private static final int ANNUAL_SCALE_FACTOR = 65;
    private static final int WORK_SCALE_FACTOR = 75;   
    private static final int MATERIALS_LIST_SCALE_FACTOR = 85;
     

    // Resum hores per treballador
    public static void reportWork(String filter) {

        if (!prepareExcel(Utils.getConfigFile().getProperty("RPT_WORK"), "")) {
            return;
        }
        logger.info("reportWork: " + filter);

        try {
            
            sheet = copy.getSheet(0);
            WritableSheet sheetGraphic = copy.getSheet(1);
            addCellLabel(1, 2, filter);
            Integer firstRow = Integer.parseInt(Utils.getConfigFile().getProperty("RPT_WORK_FIRST_ROW"));
            Integer row = firstRow;
            String sql = "SELECT * FROM var.v_rpt_work ";
            if (filter != null) {
                sql += filter;
            }
            sql += " ORDER BY data";
            logger.info("reportWork: " + sql);
            PreparedStatement ps = Utils.getConnection().prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                DateFormat df = new SimpleDateFormat("dd-MM-yyyy");
                String date = df.format(rs.getDate("data"));
                addCellLabel(0, row, date);                
                addCellLabel(1, row, rs.getString("treballador"));
                addCellLabel(2, row, rs.getString("tasca"));
                addCellLabel(3, row, rs.getString("section_description"));
                addCellNumber(4, row, rs.getDouble("treballades"));
                addCellNumber(5, row, rs.getDouble("extra_to_charge"));
                addCellNumber(6, row, rs.getDouble("extra_to_recover"));
                addCellNumber(7, row, rs.getDouble("extra_recovered"));
                addCellNumber(8, row, rs.getDouble("vacances"));
                addCellNumber(9, row, rs.getDouble("baixa"));
                addCellNumber(10, row, rs.getDouble("sindicals"));
                // Check if graphic excel details specified
                if (rs.getInt("excel_row") != 0) {
                    int excel_row = rs.getInt("excel_row");
                    int excel_col = rs.getInt("excel_col");
                    cellNumber = (Number) sheetGraphic.getCell(excel_col, excel_row);
                    double value = cellNumber.getValue();
                    value += rs.getDouble("hores");
                    cellNumber.setValue(value);
                }
                row++;
            }
            row++;
            addCellLabel(3, row, "SUMA HORES:");
            addFormula(4, row, "SUMA(E" + firstRow + ":E" + row + ")");
            addFormula(5, row, "SUMA(F" + firstRow + ":F" + row + ")");
            addFormula(6, row, "SUMA(G" + firstRow + ":G" + row + ")");
            addFormula(7, row, "SUMA(H" + firstRow + ":H" + row + ")");
            addFormula(8, row, "SUMA(I" + firstRow + ":I" + row + ")");
            addFormula(9, row, "SUMA(J" + firstRow + ":J" + row + ")");
            addFormula(10, row, "SUMA(K" + firstRow + ":K" + row + ")");
            
            SheetSettings ss = sheet.getSettings();
            ss.setScaleFactor(WORK_SCALE_FACTOR);            
            copy.write();
            copy.close();
            openExcelFile(out);
            
        } catch (SQLException | IOException | WriteException | ClassCastException ex) {
            logger.severe(ex.getMessage());
            JOptionPane.showMessageDialog(null, "Error al crear el fitxer Excel\n" + Utils.getBundle().getString("SEE_LOG"),
                "Error", JOptionPane.WARNING_MESSAGE);
        }

    }

    
    // Materials emprats per edifici
    public static void reportMaterialsBuilding(String filter, String dateStart, String dateEnd) {

        if (!prepareExcel(Utils.getConfigFile().getProperty("RPT_MATERIALS_BUILDING"), "")) {
            return;
        }

        try {
            
            sheet = copy.getSheet(0);
            WritableFont fontArial8 = new WritableFont(WritableFont.ARIAL, 8);
            WritableFont fontBoldArial8 = new WritableFont(WritableFont.ARIAL, 8, WritableFont.BOLD);
            WritableFont fontBoldArial10 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
            
            WritableCellFormat arial8 = new WritableCellFormat(fontArial8, new NumberFormat("#0.00"));
            WritableCellFormat boldArial8 = new WritableCellFormat(fontBoldArial8);
            WritableCellFormat boldArial10 = new WritableCellFormat(fontBoldArial10);
            
            arial8.setBorder(Border.LEFT, BorderLineStyle.THIN);  
            arial8.setBorder(Border.RIGHT, BorderLineStyle.THIN);  
            arial8.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.GRAY_25);  
            boldArial8.setAlignment(Alignment.RIGHT);
            boldArial10.setAlignment(Alignment.LEFT);
            
            Integer firstRow = Integer.parseInt(Utils.getConfigFile().getProperty("RPT_MATERIALS_BUILDING_FIRST_ROW"));
            Integer row = firstRow;   // 4
            String buildingId = "";
            String sql = "SELECT * FROM var.v_rpt_materials ";
            if (filter != null) {
                sql += filter;
            }
            logger.info("reportMaterialsBuilding: " + sql);
            DateFormat df = new SimpleDateFormat("dd-MM-yyyy");
            PreparedStatement ps = Utils.getConnection().prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                row++;
                // Canvi d'edifici
                if (!buildingId.equals(rs.getString("building_id"))) {
                    if (!buildingId.equals("")) {
                        sumBuilding(firstRow+1, row-1);
                        row = row + 6;
                    }
                    addCellLabel(1, row, rs.getString("building_description"), boldArial10);
                    buildingId = rs.getString("building_id");
                    row = row + 2;
                    // Escriure capçalera
                    headerMaterialBuilding(row);
                    row++;                    
                    firstRow = row;                    
                }
                
                String date = df.format(rs.getDate("date_rel"));
                addCellLabel(1, row, date, arial8);
                addCellNumber(2, row, rs.getDouble("amount"), arial8);
                String units;
                if (rs.getString("units") == null || rs.getString("units").equals("")) {
                    units = "Ut";
                } else {
                    units = rs.getString("units");
                }
                addCellLabel(3, row, units, arial8);
                addCellLabel(4, row, rs.getString("material_id"), arial8);
                addCellLabel(5, row, rs.getString("material_description"), arial8);
                sheet.mergeCells(5, row, 6, row);
                addCellNumber(7, row, rs.getDouble("price"), arial8);
                addCellNumber(8, row, rs.getDouble("total"), arial8);
            }
            
            row++;
            // Suma del darrer edifici
            sumBuilding(firstRow+1, row-1);         
            copy.write();
            copy.close();
            openExcelFile(out);
            
        } catch (SQLException | IOException | WriteException | ClassCastException ex) {
            logger.severe(ex.getMessage());
            JOptionPane.showMessageDialog(null, "Error al crear el fitxer Excel\n" + Utils.getBundle().getString("SEE_LOG"),
                "Error", JOptionPane.WARNING_MESSAGE);
        }

    }    
    
    
    private static void headerMaterialBuilding(int row) throws WriteException {
        
        WritableFont font = new WritableFont(WritableFont.ARIAL, 9, jxl.write.WritableFont.BOLD);
        WritableCellFormat wcf = new WritableCellFormat(font);
        wcf.setAlignment(Alignment.CENTRE);        
        wcf.setVerticalAlignment(VerticalAlignment.CENTRE);  
        wcf.setBackground(Colour.GRAY_25);
        wcf.setBorder(Border.ALL, BorderLineStyle.THIN);  
        addCellLabel(1, row, "Data", wcf);
        addCellLabel(2, row, "Quant.", wcf);
        addCellLabel(3, row, "Um", wcf);
        sheet.mergeCells(4, row, 6, row);
        addCellLabel(4, row, "Concepte", wcf);
        addCellLabel(7, row, "€/Um", wcf);
        addCellLabel(8, row, "€", wcf);
        
    }    
    
    
    private static void sumBuilding(int firstRow, int row) throws WriteException {
        
        WritableCellFormat wcfTotal = 
            new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD));
        wcfTotal.setAlignment(Alignment.CENTRE);        
        wcfTotal.setBorder(Border.ALL, BorderLineStyle.MEDIUM); 

        WritableCellFormat wcfTotal2 = 
            new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD), new NumberFormat("#0.00"));
        wcfTotal2.setAlignment(Alignment.RIGHT);        
        wcfTotal2.setBorder(Border.ALL, BorderLineStyle.MEDIUM); 
        
        WritableCellFormat wcfParcial = 
            new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 8));
        wcfParcial.setAlignment(Alignment.CENTRE); 
        wcfParcial.setBorder(Border.ALL, BorderLineStyle.THIN); 
             
        WritableCellFormat wcfParcial2 = 
            new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 8), new NumberFormat("#0.00"));
        wcfParcial2.setAlignment(Alignment.RIGHT);         
        wcfParcial2.setBorder(Border.ALL, BorderLineStyle.THIN);         
        
        WritableCellFormat wcfParcial3 = new WritableCellFormat();
        wcfParcial3.setBorder(Border.TOP, BorderLineStyle.THIN);    
        
        for (int i=1; i<=5; i++) {
            addCellLabel(i, row + 1, "", wcfParcial3);
        }

        addCellLabel(6, row + 1, "Total import material", wcfParcial);
        addCellLabel(6, row + 2, "5% despeses generals", wcfParcial);
        addCellLabel(6, row + 3, "Total", wcfTotal);
        sheet.mergeCells(6, row + 1, 7, row + 1);
        sheet.mergeCells(6, row + 2, 7, row + 2);
        sheet.mergeCells(6, row + 3, 7, row + 3);
        addFormula(8, row + 1, "SUMA(I" + firstRow + ":I" + (row + 1) + ")", wcfParcial2);
        addFormula(8, row + 2, "I" + (row + 2) + "*0.05)", wcfParcial2);
        addFormula(8, row + 3, "SUMA(I" + (row + 2) + ":I" + (row + 3) + ")", wcfTotal2);
        
    }
    

    // Llistat de Materials
    public static void reportMaterialsList(String filter) {

        if (!prepareExcel(Utils.getConfigFile().getProperty("RPT_MATERIALS_LIST"), "")) {
            return;
        }

        try {
            
            sheet = copy.getSheet(0);
            WritableFont fontArial9 = new WritableFont(WritableFont.ARIAL, 8);
            WritableCellFormat arial9 = new WritableCellFormat(fontArial9);

            Integer firstRow = Integer.parseInt(Utils.getConfigFile().getProperty("RPT_MATERIALS_LIST_FIRST_ROW"));
            Integer row = firstRow;
            String sql = "SELECT * FROM var.v_rpt_materials_list ";
            if (filter != null) {
                sql += filter;
            }
            logger.info("reportMaterialsList: " + sql);
            PreparedStatement ps = Utils.getConnection().prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                row++;
                addCellLabel(0, row, rs.getString(1), arial9);
                addCellLabel(1, row, rs.getString(2), arial9);
                addCellNumber(2, row, rs.getDouble(3), arial9);
                addCellNumber(3, row, rs.getDouble(4), arial9);
                addCellNumber(4, row, rs.getDouble(5), arial9);
                addCellLabel(5, row, rs.getString(6), arial9);
            }
            SheetSettings ss = sheet.getSettings();
            ss.setScaleFactor(MATERIALS_LIST_SCALE_FACTOR);   
            copy.write();
            copy.close();
            openExcelFile(out);
            
        } catch (SQLException | IOException | WriteException | ClassCastException ex) {
            logger.severe(ex.getMessage());
            JOptionPane.showMessageDialog(null, "Error al crear el fitxer Excel\n" + Utils.getBundle().getString("SEE_LOG"),
                "Error", JOptionPane.WARNING_MESSAGE);
        }

    }
    

    private static void addCellLabel(int col, int row, String label) throws WriteException {
        Label cell = new Label(col, row, label);
        sheet.addCell(cell);
    }


    private static void addCellLabel(int col, int row, String label, WritableCellFormat format) throws WriteException {
        Label cell = new Label(col, row, label, format);
        sheet.addCell(cell);
    }


    private static void addCellNumber(int col, int row, Double value) throws WriteException {
        Number cell = new Number(col, row, value);
        sheet.addCell(cell);
    }


    private static void addCellNumber(int col, int row, Double value, WritableCellFormat format) throws WriteException {
        Number cell = new Number(col, row, value, format);
        sheet.addCell(cell);
    }


    private static void addFormula(int col, int row, String form) throws WriteException {
        Formula formula = new Formula(col, row, form);
        sheet.addCell(formula);
    }


    private static void addFormula(int col, int row, String form, WritableCellFormat format) throws WriteException {
        Formula formula = new Formula(col, row, form);
        sheet.addCell(formula);
        WritableCell cell = sheet.getWritableCell(col, row);
        cell.setCellFormat(format);
    }

    
    private static boolean prepareExcel(String excelName, String function) {
        return prepareExcel(excelName, function, false);
    }

    
    private static boolean prepareExcel(String excelName, String function, boolean monthSelection) {

        File fileIn, fileOut;
        Integer year, month;

        logger = Utils.getLogger();
        if (!function.equals("")) {
            try {
                String answer = JOptionPane.showInputDialog(MainClass.mdi, "Indiqui any de l'informe", Calendar.getInstance().get(Calendar.YEAR));
                year = Integer.parseInt(answer);
                if (monthSelection){
                    answer = JOptionPane.showInputDialog(MainClass.mdi, "Indiqui mes de l'informe", Calendar.getInstance().get(Calendar.MONTH));
                    month = Integer.parseInt(answer);
                    params = new Object[2];
                    params[0] = year;
                    params[1] = month;
                } else{
                    params = new Object[1];
                    params[0] = year;
                }
                Utils.execFunction(function, params);
            } catch (NumberFormatException ex){
                logger.warning(ex.getMessage());     
                JOptionPane.showMessageDialog(null, "Període especificat incorrecte", "Error", JOptionPane.WARNING_MESSAGE);            
                return false;
            }            
        }

        in = Utils.jaspersFolder + excelName + ".xls";
        out = Utils.reportsFolder + excelName + "_" + Utils.getCurrentTimeStamp() + ".xls";
        fileIn = new File(in);
        fileOut = new File(out);
        logger.info("Plantilla: " + in + "\nInforme:   " + out);
        if (!fileIn.exists()) {
            logger.warning("No existeix el fitxer: " + in);
            return false;
        }
        Utils.copyFile(in, out);
        WorkbookSettings ws = new WorkbookSettings();
        ws.setEncoding("Cp1252");
        try {
            workbook = Workbook.getWorkbook(fileIn, ws);
            copy = Workbook.createWorkbook(fileOut, workbook);
        } catch (BiffException | IOException ex) {
            logger.severe(ex.getMessage());
            JOptionPane.showMessageDialog(null, "Error al crear el fitxer Excel\n" + Utils.getBundle().getString("SEE_LOG"),
                "Error", JOptionPane.WARNING_MESSAGE);
            return false;
        }

        return true;

    }


    public static void reportMonthSection() {

        if (!prepareExcel(Utils.getConfigFile().getProperty("RPT_MONTH_HOURS_SECTION"), "select var.set_rpt_section_month(?)")) {
            return;
        }
        logger.info("reportMonthSection");

        try {
            String query = "SELECT * FROM var.rpt_section_month WHERE section_id < 5 ORDER BY rpt_month, section_id";
            PreparedStatement ps = Utils.getConnection().prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                sheet = copy.getSheet(rs.getInt("rpt_month") - 1);
                int col = rs.getInt("section_id");
                cellNumber = (Number) sheet.getWritableCell(col, 9);
                cellNumber.setValue(rs.getDouble("worked"));
                cellNumber = (Number) sheet.getWritableCell(col, 8);
                cellNumber.setValue(rs.getDouble("extra_to_charge") + rs.getDouble("extra_to_recover"));
                cellNumber = (Number) sheet.getWritableCell(col, 10);
                cellNumber.setValue(rs.getDouble("vacation") + rs.getDouble("extra_recovered"));
                cellNumber = (Number) sheet.getWritableCell(col, 11);
                cellNumber.setValue(rs.getDouble("disable"));
                cellNumber = (Number) sheet.getWritableCell(col, 12);
                cellNumber.setValue(rs.getDouble("formation"));
                cellNumber = (Number) sheet.getWritableCell(col, 13);
                cellNumber.setValue(rs.getDouble("syndicate"));                
            }
            copy.write();
            copy.close();
            openExcelFile(out);
        } catch (SQLException | IOException | WriteException | ClassCastException ex) {
            logger.severe(ex.getMessage());
            JOptionPane.showMessageDialog(null, "Error al crear el fitxer Excel\n" + Utils.getBundle().getString("SEE_LOG"),
                "Error", JOptionPane.WARNING_MESSAGE);
        }

    }


    public static void reportAnnual() {

        if (!prepareExcel(Utils.getConfigFile().getProperty("RPT_ANNUAL"), "select var.set_rpt_annual2(?, ?)", true)) {
            return;
        }
        logger.info("reportAnnual");
        
        // Executar funció per obtenir dades de l'any anterior. Només a partir del 2014
        boolean hasPrevious = false;
        Integer year = Integer.parseInt(params[0].toString());
        Integer month = Integer.parseInt(params[1].toString());        
        if (year > YEAR_FIRST) {
            hasPrevious = true;
            Object[] paramsPrevious = new Object[2];
            paramsPrevious[0] = (year - 1);  
            paramsPrevious[1] = month;
            Utils.execFunction("select var.set_rpt_annual_previous(?, ?)", paramsPrevious);
        }

        try {
            
            // Set Fonts
            WritableFont greyColor = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD);
            WritableCellFormat wcf = new WritableCellFormat(greyColor);
            wcf.setBorder(Border.ALL, BorderLineStyle.HAIR);   
            WritableFont boldColor = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
            WritableCellFormat wcfBold = new WritableCellFormat(boldColor);
            wcfBold.setBorder(Border.ALL, BorderLineStyle.THIN);        
            
            WritableFont previousFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD);
            WritableCellFormat wcfPrevious = new WritableCellFormat(previousFont);
            wcfPrevious.setBackground(Colour.GRAY_25, Pattern.SOLID);       
            wcfPrevious.setBorder(Border.TOP, BorderLineStyle.HAIR);
            wcfPrevious.setBorder(Border.BOTTOM, BorderLineStyle.HAIR);
            wcfPrevious.setBorder(Border.LEFT, BorderLineStyle.THIN);
            wcfPrevious.setBorder(Border.RIGHT, BorderLineStyle.THIN);
            WritableFont previousFontBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
            WritableCellFormat wcfPreviousBold = new WritableCellFormat(previousFontBold);
            wcfPreviousBold.setBackground(Colour.GRAY_25, Pattern.SOLID);
            wcfPreviousBold.setBorder(Border.ALL, BorderLineStyle.THIN);    
            
            // Set headers
            sheet = copy.getSheet(0);            
            String monthName = Utils.getMonthName(month);
            cellLabel = (Label) sheet.getWritableCell(13, 0);      
            cellLabel.setString(monthName + " " + year.toString());  
            if (hasPrevious) {
                year--;            	
                cellLabel = (Label) sheet.getWritableCell(16, 4);     
                cellLabel.setString(monthName + " " + year.toString());             	
            }
            
            // Set values
            String query = "SELECT * FROM var.rpt_annual WHERE excel_row is not null ORDER BY excel_row";
            PreparedStatement ps = Utils.getConnection().prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int row = rs.getInt("excel_row") - 1;
                double valuePrevious = 0.0;
                for (int i = 1; i <= 12; i++) {
                    double value = rs.getDouble("month_" + i);
                    if (rs.getInt("type") == 2){
                        cellNumber = new Number(i + 1, row, value, wcfBold);
                    }
                    else{
                        cellNumber = new Number(i + 1, row, value, wcf);
                    }
                    sheet.addCell(cellNumber);
                    valuePrevious+= rs.getDouble("month_previous_" + i);                    
                }
                if (hasPrevious){
                    if (rs.getInt("type") == 2){
                        cellNumber = new Number(17, row, valuePrevious, wcfPreviousBold);
                    }
                    else{
                        cellNumber = new Number(17, row, valuePrevious, wcfPrevious);
                    }
                    sheet.addCell(cellNumber);           
                }
            }
            
            SheetSettings ss = sheet.getSettings();
            ss.setScaleFactor(ANNUAL_SCALE_FACTOR);
            copy.write();
            copy.close();
            openExcelFile(out);
            
        } catch (SQLException | IOException | WriteException | ClassCastException ex) {
            logger.severe(ex.getMessage());
            JOptionPane.showMessageDialog(null, "Error al crear el fitxer Excel\n" + Utils.getBundle().getString("SEE_LOG"),
                "Error", JOptionPane.WARNING_MESSAGE);
        }

    }

      
    // Funciona correctament excepte quan hi ha més d'una localització en algun treball    
    public static void reportMonthly() {

        if (!prepareExcel(Utils.getConfigFile().getProperty("RPT_MONTH"), "", true)) {
            return;
        }
        logger.info("reportMonthly");

        try {
            
            Integer year, month;
            int total = 0;
            int totalTask = 0;
            try {
                String answer = JOptionPane.showInputDialog(MainClass.mdi, "Indiqui any de l'informe", Calendar.getInstance().get(Calendar.YEAR));
                year = Integer.parseInt(answer);
                answer = JOptionPane.showInputDialog(MainClass.mdi, "Indiqui mes de l'informe", Calendar.getInstance().get(Calendar.MONTH));
                month = Integer.parseInt(answer);
            } catch (NumberFormatException ex){
                logger.warning(ex.getMessage());     
                JOptionPane.showMessageDialog(null, "Període especificat incorrecte", "Error", JOptionPane.WARNING_MESSAGE);            
                return;
            }  
            sheet = copy.getSheet(0);
            
            // Set headers
            String monthName = Utils.getMonthName(month);
            cellLabel = (Label) sheet.getWritableCell(4, 0);      
            cellLabel.setString(monthName);
            cellLabel = (Label) sheet.getWritableCell(6, 0);      
            cellLabel.setString(year.toString());
            
            // Set fonts
            WritableFont greyColor = new WritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD);
            WritableCellFormat wcf = new WritableCellFormat(greyColor);
            wcf.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.GRAY_25);     
            wcf.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.GRAY_25);                   
            wcf.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.GRAY_25);     
            WritableCellFormat wcf2 = new WritableCellFormat(greyColor);
            wcf2.setBorder(Border.ALL, BorderLineStyle.THIN);   
            WritableCellFormat wcfType2 = new WritableCellFormat(greyColor);
            wcfType2.setBorder(Border.TOP, BorderLineStyle.MEDIUM);    
            wcfType2.setBorder(Border.BOTTOM, BorderLineStyle.MEDIUM); 
            wcfType2.setBorder(Border.LEFT, BorderLineStyle.THIN); 
            wcfType2.setBorder(Border.RIGHT, BorderLineStyle.THIN); 
            
            // Set borders
            WritableCellFormat wcfLeft = new WritableCellFormat();                
            WritableCellFormat wcfRight = new WritableCellFormat();                    
            wcfLeft.setBorder(Border.LEFT, BorderLineStyle.THIN);                    
            wcfLeft.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.GRAY_25);   
            wcfRight.setBorder(Border.RIGHT, BorderLineStyle.THIN);       
            wcfRight.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.GRAY_25);                
                 
            String query = "SELECT w.task_id, wl.location_group_id, wl.location_id, w.comments, r.description, SUM(w.worked) as suma "
                + "FROM work w LEFT JOIN work_location wl ON w.id = wl.work_id INNER JOIN rpt_annual r ON w.task_id = r.id " 
                + "WHERE extract(year from date_work) = " + year + " AND extract(month from date_work) = " + month + " AND worked is not null "
                + "GROUP BY excel_row, task_id, location_group_id, location_id, w.comments, r.description "
                + "ORDER BY excel_row, location_id, comments DESC";
            PreparedStatement ps = Utils.getConnection().prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            String taskIdPrevious = "";   
            String descPrevious = "";
            int row = 0;  
            Double hours = 0.0;
            while (rs.next()) {
                
                // Determine if we have changed task or not
                String taskId = rs.getString("task_id");              
                if (!taskId.equals(taskIdPrevious)) {

                    // Add sum calculation                    
                    if (!taskIdPrevious.equals("")) {
                        int type = getTaskType(taskIdPrevious);
                        WritableCellFormat cellFormat;                        
                        // If has no children
                        if (type == 2){
                            WritableFont fontBold10 = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD);
                            cellFormat = new WritableCellFormat(fontBold10);
                            cellFormat.setBorder(Border.TOP, BorderLineStyle.MEDIUM);                               
                            cellFormat.setBorder(Border.BOTTOM, BorderLineStyle.MEDIUM);                                                           
                            cellFormat.setBorder(Border.LEFT, BorderLineStyle.THIN);
                            cellFormat.setBorder(Border.RIGHT, BorderLineStyle.THIN);
                        } else{
                            WritableFont font10 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD);                            
                            cellFormat = new WritableCellFormat(font10);
                            cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);                               
                        }
                        row++;
                        addFormula(5, row + totalTask - 1, "SUMA(E" + (row) + ":E" + (row + totalTask) + ")", cellFormat);   
                        cellNumber = new Number(4, row, hours, cellFormat);                        
                        if (totalTask == 1) {
                            cellNumber = new Number(5, row, hours, cellFormat);
                            sheet.addCell(cellNumber);    
                        	// Copiar dades de localització i comentaris
                            cellLabel = (Label) sheet.getCell(2, row - 1);
                            String loc = cellLabel.getString();
                            if (!loc.equals("")){
                            	if (type != 2){
                            		cellLabel = new Label(2, row, loc, wcf2);
                            	} else{
                            		cellLabel = new Label(2, row, loc, wcfType2);
                            	}
                            	sheet.addCell(cellLabel);   
                            }
                            cellLabel = (Label) sheet.getCell(3, row - 1);
                            String com = cellLabel.getString();
                            if (!com.equals("") && !com.toLowerCase().equals(descPrevious)){
                            	if (type != 2){
                            		cellLabel = new Label(3, row, com, wcf2);
                            	} else{
                            		cellLabel = new Label(3, row, com, wcfType2);
                            	}
                            	sheet.addCell(cellLabel);   
                            }     
                            sheet.removeRow(row - 1);
                        } else {
                            total = total + totalTask;                            
                        }
                    }
                    
                    descPrevious = rs.getString("description").toLowerCase();                    
                    taskIdPrevious = taskId;
                    totalTask = 0;
                    // Get first row of current task_id                    
                    String query2 = "SELECT excel_row FROM var.rpt_annual WHERE id = '" + taskId + "'"; 
                    PreparedStatement ps2 = Utils.getConnection().prepareStatement(query2);
                    ResultSet rs2 = ps2.executeQuery();   
                    if (rs2.next()) {
                        row = rs2.getInt("excel_row");
                        row--;
                        row = row + total;
                        rs2.close();
                    }   
                    
                }
    
                // Insert new blank row with borders
                sheet.insertRow(row);              

                // Set borders
                cellLabel = new Label(1, row, "", wcf);
                sheet.addCell(cellLabel);
                cellLabel = new Label(5, row, "", wcf);
                sheet.addCell(cellLabel);
                cellLabel = new Label(0, row, "", wcfLeft);
                sheet.addCell(cellLabel);           
                cellLabel = new Label(6, row, "", wcfRight);
                sheet.addCell(cellLabel);

                // Copy values
                String location = getLocationName(rs.getInt("location_group_id"), rs.getString("location_id"));
                cellLabel = new Label(2, row, location, wcf);
                sheet.addCell(cellLabel);
                String comments = rs.getString("comments");
                if (location.equals("") && comments.equals("")){
                    comments = rs.getString("description");
                }
                cellLabel = new Label(3, row, comments, wcf);
                sheet.addCell(cellLabel);
                Double valor2 = rs.getDouble("suma");
                cellNumber = new Number(4, row, valor2, wcf);
                sheet.addCell(cellNumber);
                hours = valor2;                

                totalTask++;                    

            }  // end while

            // Insert sum of the last taskId
            int type = getTaskType(taskIdPrevious);
            WritableCellFormat cellFormat;                        
            // If has no children
            if (type == 2) {
                WritableFont fontBold10 = new WritableFont(WritableFont.ARIAL, 11, WritableFont.BOLD);
                cellFormat = new WritableCellFormat(fontBold10);
                cellFormat.setBorder(Border.TOP, BorderLineStyle.MEDIUM);                               
                cellFormat.setBorder(Border.BOTTOM, BorderLineStyle.MEDIUM);                                                           
                cellFormat.setBorder(Border.LEFT, BorderLineStyle.THIN);
                cellFormat.setBorder(Border.RIGHT, BorderLineStyle.THIN);
            } else {
                WritableFont font10 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD);                            
                cellFormat = new WritableCellFormat(font10);
                cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);                               
            }
            row++;
            addFormula(5, row + totalTask - 1, "SUMA(E" + (row) + ":E" + (row + totalTask) + ")", cellFormat);   
            cellNumber = new Number(4, row, hours, cellFormat);                        
            if (totalTask == 1) {
                cellNumber = new Number(5, row, hours, cellFormat);
                sheet.addCell(cellNumber);                    
                sheet.removeRow(row - 1);
            } 
            
            SheetSettings ss = sheet.getSettings();
            ss.setFitHeight(MONTHLY_FIT_HEIGHT);
            ss.setFitWidth(1);
        
            copy.write();
            copy.close();
            openExcelFile(out);
            
        } catch (SQLException | IOException | WriteException | ClassCastException ex) {
            logger.severe(ex.getMessage());
            JOptionPane.showMessageDialog(null, "Error al crear el fitxer Excel\n" + Utils.getBundle().getString("SEE_LOG"),
                "Error", JOptionPane.WARNING_MESSAGE);
        }

    }

    
    private static String getLocationName(int locationGroupId, String locationId) throws SQLException {
    
        if (locationGroupId == 0) {
            return "";
        }
        
        String query = "SELECT table_name_sql, table_id_sql, table_id_sql, table_description_sql "
            + "FROM location_group WHERE id = " + locationGroupId;
        PreparedStatement ps = Utils.getConnection().prepareStatement(query);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            query = "SELECT " + rs.getString("table_description_sql") + " as desc"  
                + " FROM " + rs.getString("table_name_sql")
                + " WHERE " + rs.getString("table_id_sql") + " = '" + locationId + "'";
        }
        rs.close();
        
        // Get location details from specific location table
        ps = Utils.getConnection().prepareStatement(query);
        rs = ps.executeQuery();        
        if (rs.next()){
            return rs.getString("desc");
        }        
        return "";
        
    }

    
    private static int getTaskType(String taskIdPrevious) throws SQLException {
        
        String query = "SELECT type FROM rpt_annual WHERE id = '" + taskIdPrevious + "'";
        PreparedStatement ps = Utils.getConnection().prepareStatement(query);
        ResultSet rs = ps.executeQuery();
        int type = -1;
        if (rs.next()) {
            type = rs.getInt("type");
        }
        return type;
        
    }
    

    private static void openExcelFile(String file) {
        
        try {
            int res = Utils.askQuestionYesNo(null, "Informe generat a: \n" + file + "\nDesitja obrir-lo?", "Informe generat");
            if (res == 0) {
                Process p = Runtime.getRuntime().exec("rundll32 url.dll,FileProtocolHandler " + file);
                p.waitFor();
            }
        } catch (IOException | InterruptedException ex) {
            logger.severe(ex.getMessage());
            JOptionPane.showMessageDialog(null, "Error a l'obrir el fitxer Excel\n" + Utils.getBundle().getString("SEE_LOG"),
                "Error", JOptionPane.WARNING_MESSAGE);
        }
        
    }

    
}